<?php

/**
 * This is the model class for table "prices".
 *
 * The followings are the available columns in table 'prices':
 * @property integer $id
 * @property integer $crop_id
 * @property double $amount
 * @property string $effective_date
 * @property string $timestamp
 *
 * The followings are the available model relations:
 * @property Crops $crop
 */
class Price extends CActiveRecord {

    /**
     * Returns the static model of the specified AR class.
     * @param string $className active record class name.
     * @return Price the static model class
     */
    public static function model($className = __CLASS__) {
        return parent::model($className);
    }

    /**
     * @return string the associated database table name
     */
    public function tableName() {
        return 'prices';
    }

    /**
     * @return array validation rules for model attributes.
     */
    public function rules() {
        return array(
            array('crop_id, amount', 'required'),
            array('amount', 'numerical'),
        );
    }

    /**
     * @return array relational rules.
     */
    public function relations() {
        return array(
            'crop' => array(self::BELONGS_TO, 'Crop', 'crop_id'),
        );
    }

    /**
     * @return array customized attribute labels (name=>label)
     */
    public function attributeLabels() {
        return array(
            'id' => 'ID',
            'crop_id' => 'Crop',
            'amount' => 'Amount',
            'effective_date' => 'Effective Date',
            'timestamp' => 'Timestamp',
        );
    }

    /**
     * Retrieves a list of models based on the current search/filter conditions.
     * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
     */
    public function search() {

        $criteria = new CDbCriteria;

        $criteria->compare('id', $this->id);
        $criteria->compare('crop_id', $this->crop_id);
        $criteria->compare('amount', $this->amount);
        $criteria->compare('effective_date', $this->effective_date, true);

        return new CActiveDataProvider($this, array(
                    'criteria' => $criteria,
                ));
    }

    public function beforeSave() {
        $this->effective_date = new CDbExpression('CURDATE()');

        return parent::beforeSave();
    }

    public function queryRecent($cropType, $groupId) {

        $command = Yii::app()->db->createCommand()
                ->select('c.variety AS variety, p.amount AS price, p.effective_date AS date')
                ->from('crops c')
                ->leftJoin('prices p', 'p.crop_id = c.id')
                ->leftJoin('groups g', 'g.id = c.group_id')
                ->where('p.id IN (SELECT MAX(id) FROM prices GROUP BY crop_id) 
                        AND c.type = :cropType
                        AND c.group_id = :groupId', array(':cropType' => $cropType, ':groupId' => $groupId)
        );
        $dataReader = $command->queryAll();

        if ($dataReader != null) {
            foreach ($dataReader as $row) {
                $prices[] = array(
                    'variety' => $row['variety'],
                    'price' => $row['price'],
                    'date' => $row['date']
                );
            }
            
            $rows[Group::model()->find('id=:id', array(':id' => $groupId))->name] = $prices;

            return $rows;
        }

        return false;
    }

    public function queryPending($lastBroadcast = null) {

        $sql = 'SELECT c.alias AS crop_alias, p.amount AS crop_price, p.effective_date AS price_effective_date
                FROM crops AS c
                JOIN prices AS p ON p.crop_id = c.id
                JOIN groups AS g ON g.id = c.group_id
                WHERE g.id = :groupId ';
        if ($lastBroadcast !== null)
            $sql .= 'AND TIMESTAMPDIFF(SECOND, g.last_broadcast_time, p.timestamp) > 0 ';
        $sql .= 'ORDER BY p.effective_datetime DESC';

        $command = Yii::app()->db->createCommand($sql);
        $groupId = Yii::app()->user->instance->group_id;
        $command->bindParam(':groupId', $groupId, PDO::PARAM_INT);
        $dataReader = $command->queryAll();

        if ($dataReader != null) {
            $rows = array();

            foreach ($dataReader as $row) {
                $rows[] = array(
                    $row['crop_alias'] => array(
                        'price' => $row['crop_price'],
                        'effective_datetime' => $row['price_effective_datetime'],
                    )
                );
            }

            $rows = array(Yii::app()->user->instance->group->name => $rows);

            return $rows;
        }

        return false;
    }

}